package org.opens.db;

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import org.opens.db.column.ani.TableColumn;
import org.opens.db.column.ani.TableColumns;
import org.opens.db.constant.DbType;
import org.opens.util.DatabaseConnectInfoEntity;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 功能描述
 *
 * @author by zhangyuming
 * @date 2023/1/14
 */
public class JdbcUtil {

    private static Log log = LogFactory.get(JdbcUtil.class);

    /**
     * 封装的查询方法
     * @param url           数据库连接url
     * @param username      用户名
     * @param password      密码
     * @param resultClazz   结果类型Class
     * @param sql           执行的sql
     * @param params        参数
     * @return  查询到的结果
     */
    public static <T> List<T> query(String url, String username, String password, Class<T> resultClazz, String sql, Object... params) {
        Assert.notBlank(url, "数据库连接url不能为空!");
        Assert.notBlank(username, "数据库连接username不能为空!");
        Assert.notBlank(password, "数据库连接password不能为空!");
        Assert.notNull(resultClazz, "结果集类型未设置!");
        Assert.notBlank(sql, "sql不能为空!");

        List<T> resultList = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DynamicDataSourceUtil.getConnection(url, username, password);
            preparedStatement = connection.prepareStatement(sql);

            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                int index = i + 1;
                if (param instanceof Integer) {
                    preparedStatement.setInt(index, (int)param);
                } else if (param instanceof String) {
                    preparedStatement.setString(index, param.toString());
                } else if (param instanceof Timestamp) {
                    preparedStatement.setTimestamp(index, (Timestamp)param);
                } else if (param instanceof Long) {
                    preparedStatement.setLong(index, (long)param);
                } else {
                    log.warn("未匹配到有效的类型映射, 使用了默认的String, 实际的类型: {}", param.getClass());
                    preparedStatement.setString(index, param.toString());
                }
            }

            resultSet = preparedStatement.executeQuery();
            DbType dbType = DynamicDataSourceUtil.extractDatabaseTypeFromURL(url);
            Assert.notNull(dbType, "未从url({})提取到合适的数据库类型!", url);

            while (resultSet.next()) {
                T entity = ReflectUtil.newInstance(resultClazz);
                Map<String, Field> fieldMap = ReflectUtil.getFieldMap(resultClazz);

                Iterator<Map.Entry<String, Field>> fieldMapIterator = fieldMap.entrySet().iterator();
                while (fieldMapIterator.hasNext()) {
                    Map.Entry<String, Field> nextEntry = fieldMapIterator.next();
                    Field field = nextEntry.getValue();

                    // 注解映射的字段名: 高优先级
                    String mappingKey = extractFiledAniValue(field, dbType);
                    // 实体类的直接字段名: 中优先级
                    String entryKey = nextEntry.getKey();
                    // 驼峰转下划线后的字段名: 低优先级
                    String underlineCaseKey = StrUtil.toUnderlineCase(nextEntry.getKey());
                    // 最终采用的key
                    String realKey = null;

                    // 优先级筛选
                    // 注意: mappingKey可能为空, 但是entryKey和underlineCaseKey是计算出来的, 不可能为空, 因此只需要对mappingKey判空.
                    if (StrUtil.isNotBlank(mappingKey) && isExistColumn(resultSet, mappingKey)) {
                        realKey = mappingKey;
                    } else if (isExistColumn(resultSet, entryKey)) {
                        realKey = entryKey;
                    } else if (isExistColumn(resultSet, underlineCaseKey)) {
                        realKey = underlineCaseKey;
                    } else {
                        // 找不到合适的列就跳过本次循环
                        // log.warn("列{}不存在!", key);
                        continue;
                    }

                    // 此处目前是直接给字段赋值, 如果有需要的话要改成通过setter
                    if (Integer.class.equals(field.getType())) {
                        int columnValue = resultSet.getInt(realKey);
                        ReflectUtil.setFieldValue(entity, field, columnValue);
                    } else if (String.class.equals(field.getType())) {
                        String columnValue = resultSet.getString(realKey);
                        ReflectUtil.setFieldValue(entity, field, columnValue);
                    } else if (Timestamp.class.equals(field.getType())) {
                        Timestamp columnValue = resultSet.getTimestamp(realKey);
                        ReflectUtil.setFieldValue(entity, field, columnValue);
                    } else {
                        log.warn("未匹配到有效的类型映射, 使用了默认的String, 实际的类型: {}", field.getType());
                        String columnValue = resultSet.getString(nextEntry.getKey());
                        ReflectUtil.setFieldValue(entity, field, columnValue);
                    }
                }

                resultList.add(entity);
            }

        } catch (SQLException e) {
            // 如果发生SQL相关的异常, 直接抛出
            throw new RuntimeException(e);
        } finally {
            IoUtil.close(resultSet);
            IoUtil.close(preparedStatement);
        }

        return resultList;
    }

    public static <T> List<T> query(DatabaseConnectInfoEntity databaseConnectInfo, Class<T> resultClazz, String sql, Object... params) {
        Assert.notNull(databaseConnectInfo, "数据库连接参数不能为空!");
        return query(databaseConnectInfo.getUrl(), databaseConnectInfo.getUsername(), databaseConnectInfo.getPassword(), resultClazz, sql, params);
    }

    /**
     * 判断查询结果集中是否存在某列
     * @param rs 查询结果集
     * @param columnName 列名
     * @return true 存在; false 不存咋
     */
    public static boolean isExistColumn(ResultSet rs, String columnName) {
        Assert.notNull(rs, "jdbc结果集不能为null!");
        Assert.notBlank(columnName, "判断的列名不能为空!");
        try {
            return rs.findColumn(columnName) > 0;
        } catch (SQLException e) {
            return false;
        }
    }

    /**
     * 提取反射字段注解中的value值
     * @param field     反射字段
     * @param dbType    数据库类型
     * @return  提取到的value值
     */
    private static String extractFiledAniValue(Field field, DbType dbType) {
        Assert.notNull(field, "提取字段不能为空!");
        Assert.notNull(dbType, "提取数据库类型不能为空!");

        String value = null;

        TableColumns tableColumns = field.getDeclaredAnnotation(TableColumns.class);
        if (tableColumns != null) {
            TableColumn[] columns = tableColumns.value();
            if (columns != null && columns.length != 0) {
                TableColumn currentTableColumn = null;
                for (int i = 0; i < columns.length; i++) {
                    if (dbType.equals(columns[i].dbType())) {
                        currentTableColumn = columns[i];
                        break;
                    }
                }
                if (currentTableColumn != null) {
                    value = currentTableColumn.value();
                }
            }
        }

        return value;
    }

    /**
     * 计算表的别名
     * - 例如: abc_qwe -> aq
     * @param tableName 表名
     * @return  表的别名
     */
    public static String calcTableAlias(String tableName) {
        Assert.notBlank(tableName, "tableName can not be empty!");

        String[] tableNameSplitArr = tableName.split("_");

        //默认值不能是null, 因为null += "s" -> "nulls"
        String tableAlias = "";

        for (int i = 0; i < tableNameSplitArr.length; i++) {
            tableAlias += tableNameSplitArr[i].substring(0, 1);
        }

        return tableAlias;
    }

}
